package com.iris.live.services.data.repositories.impl;

import com.google.common.base.Strings;
import com.google.common.collect.Maps;
import com.iris.live.services.common.reconsitution.FinanceConstant;
import com.iris.live.services.data.repositories.FinanceReceivablesRepository;
import com.iris.live.services.models.PageData;
import com.iris.live.services.requestbody.FinanceReceivablesBody;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 财务系统对接模块(新车收款模块)--持久层
 */
@Repository
public class FinanceReceivablesRepositoryImpl extends BaseRepository implements FinanceReceivablesRepository{

    @Override
    public List getOrderDetail(String quotedNum) {
        String sql = "select a.*,a.订单总价-a.收款金额-a.挂账金额+a.已退金额 as 应收金额,a.订单总价-已开票金额 as 未开票金额\n" +
                "from(\n" +
                " select \n" +
                "  b.*,\n" +
                "  round(IFNULL(c.保险总价,0)+IFNULL(c.车船税,0)+IFNULL(i.购置税,0)+IFNULL(d.上牌服务费,0.0)+IFNULL(d.其他服务费,0)+IFNULL(g.金融服务费,0)+   \n" +
                "  IFNULL(f.延保折后价,0)+IFNULL(e.精品折后价,0)+ifnull(a.折后价,0)+   \n" +
                "  IFNULL(h.置换手续费,0)+IFNULL(f.厂家延保实收,0)+IFNULL(f.第三方延保实收,0)+IFNULL(i.代金券,0)+   \n" +
                "  IFNULL(i.会员费,0)+IFNULL(i.续保服务费,0)+IFNULL(i.贷款抵押费,0)+IFNULL(i.二手车押金,0)+   \n" +
                "  IFNULL(i.上牌保证金,0)+IFNULL(i.车辆管理费,0)+IFNULL(i.常规保养,0)+IFNULL(i.其他六,0)+   \n" +
                "  IFNULL(i.其他七,0)+IFNULL(i.其他一,0)+IFNULL(i.其他二,0)+IFNULL(i.其他三,0)+IFNULL(i.其他四,0)+IFNULL(i.其他五,0),2) 订单总价 \n" +
                " from(\n" +
                "  select\n" +
                "   a.报价编号,\n" +
                "   a.初始报价单号,\n" +
                "   e.财务编号,\n" +
                "   case \n" +
                //"     when c.订单状态 = '合同C' and not isnull(i.合同签约日期) then '合同' \n" +
                "     when b.订单状态 = '意向订单C' and not isnull(b.订单审批通过日期) and date(b.订单审批通过日期) >= ifnull(b.订单失销日期,'1970-01-01') and b.订金类型 = '意向金'  then '意向订单'\n" +
                "     when b.订单状态 = '订单C' and not isnull(b.订单审批通过日期) and date(b.订单审批通过日期) >= ifnull(b.订单失销日期,'1970-01-01') and b.订金类型 = '订金' then '订单'\n" +
                "     when b.订单状态 is null or b.订单状态 = '退订C' then '退订单'\n" +
                "     else '合同'\n" +
                "   end as 订单类型,\n" +
                "   a.初始报价单号 订单号,\n" +
                "   b.销售类型,\n" +
                "   c.顾客姓名 客户姓名,\n" +
                "   c.电话号码 联系电话,\n" +
                "   g.身份证 身份证号码,\n" +
                "   h.`人员姓名` 销售顾问,\n" +
                "   d.车架号,\n" +
                "   ifnull(e.挂账金额,0)+ifnull(e.红冲挂账金额,0) 挂账金额,\n" +
                "   ifnull(e.退款金额,0)+ifnull(e.红冲退款金额,0) 已退金额,\n" +
                "   ifnull(e.收款金额,0)+ifnull(e.红冲收款金额,0) 收款金额,\n" +
                "   ifnull(e.核销金额,0)+ifnull(e.红冲核销金额,0) 核销金额,\n" +
                "   ifnull(e.开票金额,0)+ifnull(e.退票金额,0) 已开票金额,\n" +
                "   ifnull(e.退票金额,0) 退票金额,\n" +
                "   case e.开票状态 when '01' then '完成' else '未完成' end as 发票登记状态,\n" +
                "   case e.收款状态 when '01' then '完成' else '未完成' end as 收款状态,\n" +
                "   j.企业名称 发票抬头,\n" +
                "   j.纳税人识别号,\n" +
                "   j.企业电话 开票人电话,\n" +
                "   j.开户行,\n" +
                "   j.账号,\n" +
                "   concat(ifnull(g.省份,''),ifnull(g.城市,''),ifnull(g.区域,''),ifnull(g.详细地址,'')) 开票人地址\n" +
                "  from 报价单表 a\n" +
                "  left join 报价订单表 b on a.订单编号 = b.订单编号\n" +
                "  left join 顾客记录汇总 c on a.顾客编号 = c.顾客编号\n" +
                "  left join lsh库存表 d on a.库存编号 = d.库存编号\n" +
                "  left join 销售财务表 e on a.初始报价单号 = e.`订单编号`\n" +
                "  left join 报价交车表 f on a.交车 = f.编号\n" +
                "  left join 报价客户表 g on a.客户详细 = g.编号\n" +
                "  left join 用户 h on a.销售顾问 = h.用户名\n" +
                "  left join 报价交车表 j on a.交车 = j.编号\n" +
                "  where a.报价编号 = :quotedNum\n" +
                " ) b \n" +
                " join 报价单表 a on b.报价编号 = a.报价编号\n" +
                " LEFT JOIN (  \n" +
                "  SELECT SUM( IF ( `险种` = '交强险', `折后价`, 0 )) + SUM( IF ( `险种` = '交强险', `不计免赔`, 0 )) AS 交强险,  \n" +
                "   SUM( IF ( `险种` = '车船税', `折后价`, 0 )) + SUM( IF ( `险种` = '车船税', `不计免赔`, 0 )) AS 车船税,  \n" +
                "   SUM( IF ( `险种` <> '交强险'&&`险种`<>'车船税', `折后价`, 0 )) + SUM( IF ( `险种` <> '交强险'&&`险种`<>'车船税', `不计免赔`, 0 )) AS 商业险,  \n" +
                "   SUM( IF ( `险种` != '车船税', `折后价`, 0 )) + SUM( IF ( `险种` != '车船税', `不计免赔`, 0 )) AS 保险总价,保险编号 \n" +
                "  FROM `报价保险详细表` \n" +
                "  WHERE `保险编号` = (select 保险编号 from 报价单表 where 报价编号 = :quotedNum)   \n" +
                " ) c ON a.保险编号 = c.保险编号   \n" +
                " LEFT JOIN (select MAX(case when 费用名称 = '上牌服务费' then 费用折后价 end) 上牌服务费 ,      \n" +
                "  MAX(case when 费用名称 = '其他服务费' then 费用折后价 end) 其他服务费,      \n" +
                "  费用编号      \n" +
                "  from 报价费用详细表 GROUP BY 费用编号      \n" +
                "  ) d ON a.费用编号 = d.费用编号      \n" +
                " LEFT JOIN 报价精品表 e ON  a.精品 = e.编号      \n" +
                " LEFT JOIN 报价五星延保 f ON f.延保编号 = a.延保编号      \n" +
                " LEFT JOIN 报价金融表 g ON g.金融编号 = a.金融    \n" +
                " LEFT JOIN   报价置换表 h ON a.置换编号 = h.置换编号    \n" +
                " LEFT JOIN 报价预收表 i ON a.预收编号 = i.预收编号\n" +
                ") a";
        Map params = new HashMap();
        params.put("quotedNum",quotedNum);
        return this.getList(sql,params);
    }

    @Override
    public PageData getMoneyHistory(String quotedNum,Integer pageSize,Integer pageIndex) {
        List<String> typeList = new ArrayList();
        typeList.add(FinanceConstant.SHOUKUAN);
        typeList.add(FinanceConstant.TUIKUAN);
        typeList.add(FinanceConstant.GUAZHANG);
        typeList.add(FinanceConstant.HCSK);
        typeList.add(FinanceConstant.HCTK);
        typeList.add(FinanceConstant.HCGZ);
        String sql = "select \n" +
                "b.财务编号,\n" +
                "b.账目编号,\n" +
                " b.账目日期,\n" +
                " b.账目单号,\n" +
                " b.账目类型,\n" +
                " case when b.账目类型 in('收款','红冲收款') then b.支付方式 when b.账目类型 in('退款','红冲退款') then b.退款方式 when b.账目类型 in('挂账','红冲挂账') then b.挂账类别 else '' end '支付方式/挂账类型',\n" +
                " b.账目金额,\n" +
                " b.交易凭证号,\n" +
                " b.备注,\n" +
                " case b.是否红冲 when '1' then '是' else '否' end 是否红冲,\n" +
                " d.人员姓名 操作人,\n" +
                " case b.同步状态  when '1' then '成功' when '2' then '失败' else '未传送' end 传送结果,\n" +
                " b.同步日期 传送日期,\n" +
                " b.退款原因,\n" +
                " b.操作日期 操作时间\n" +
                "from 销售财务表 a \n" +
                "left join 销售财务账目表 b on a.财务编号 = b.`财务编号`\n" +
                "join 报价单表 c on a.订单编号 = c.初始报价单号\n" +
                "join 用户 d on b.修改用户 = d.用户名 \n" +
                "where c.报价编号 = :quotedNum\n" +
                "and b.账目类型 in (:typeList)" +
                "order by b.修改时间 desc,b.创建时间 desc\n";
        Map params = new HashMap();
        params.put("quotedNum",quotedNum);
        params.put("typeList",typeList);
        return this.getPage(sql,params,pageSize,pageIndex);
    }

    @Override
    public BigDecimal getOrderTotalPrice(String quotedNum) {
        String sql = "select \n" +
                " a.报价编号,\n" +
                " IFNULL(c.保险总价,0)+IFNULL(c.车船税,0)+IFNULL(i.购置税,0)+IFNULL(d.上牌服务费,0.0)+IFNULL(d.其他服务费,0)+IFNULL(g.金融服务费,0)+   \n" +
                " IFNULL(f.延保折后价,0)+IFNULL(e.精品折后价,0)+ifnull(a.折后价,0)+   \n" +
                " IFNULL(h.置换手续费,0)+IFNULL(f.厂家延保实收,0)+IFNULL(f.第三方延保实收,0)+IFNULL(i.代金券,0)+   \n" +
                " IFNULL(i.会员费,0)+IFNULL(i.续保服务费,0)+IFNULL(i.贷款抵押费,0)+IFNULL(i.二手车押金,0)+   \n" +
                " IFNULL(i.上牌保证金,0)+IFNULL(i.车辆管理费,0)+IFNULL(i.常规保养,0)+IFNULL(i.其他六,0)+   \n" +
                " IFNULL(i.其他七,0)+IFNULL(i.其他一,0)+IFNULL(i.其他二,0)+IFNULL(i.其他三,0)+IFNULL(i.其他四,0)+IFNULL(i.其他五,0) 订单总价\n" +
                "FROM 报价单表 a \n" +
                "LEFT JOIN (  \n" +
                "  SELECT SUM( IF ( `险种` = '交强险', `折后价`, 0 )) + SUM( IF ( `险种` = '交强险', `不计免赔`, 0 )) AS 交强险,  \n" +
                "   SUM( IF ( `险种` = '车船税', `折后价`, 0 )) + SUM( IF ( `险种` = '车船税', `不计免赔`, 0 )) AS 车船税,  \n" +
                "   SUM( IF ( `险种` <> '交强险'&&`险种`<>'车船税', `折后价`, 0 )) + SUM( IF ( `险种` <> '交强险'&&`险种`<>'车船税', `不计免赔`, 0 )) AS 商业险,  \n" +
                "   SUM( IF ( `险种` != '车船税', `折后价`, 0 )) + SUM( IF ( `险种` != '车船税', `不计免赔`, 0 )) AS 保险总价,保险编号 \n" +
                "  FROM `报价保险详细表`  \n" +
                "  WHERE `保险编号` = (select 保险编号 from 报价单表 where 报价编号 = :quotedNum)   \n" +
                ") c ON a.保险编号 = c.保险编号   \n" +
                "LEFT JOIN (select MAX(case when 费用名称 = '上牌服务费' then 费用折后价 end) 上牌服务费 ,      \n" +
                " MAX(case when 费用名称 = '其他服务费' then 费用折后价 end) 其他服务费,      \n" +
                " 费用编号      \n" +
                " from 报价费用详细表 GROUP BY 费用编号      \n" +
                " ) d ON a.费用编号 = d.费用编号      \n" +
                "LEFT JOIN 报价精品表 e ON  a.精品 = e.编号      \n" +
                "LEFT JOIN 报价五星延保 f ON f.延保编号 = a.延保编号      \n" +
                "LEFT JOIN 报价金融表 g ON g.金融编号 = a.金融    \n" +
                "LEFT JOIN   报价置换表 h ON a.置换编号 = h.置换编号    \n" +
                "LEFT JOIN 报价预收表 i ON a.预收编号 = i.预收编号\n" +
                "where a.报价编号 = :quotedNum";
        Map params = new HashMap();
        params.put("quotedNum",quotedNum);
        List<Map> list = this.getList(sql,params);
        if (list != null && !list.isEmpty()) {
            return new BigDecimal(list.get(0).get("订单总价").toString());
        }
        return new BigDecimal(0);
    }

    @Override
    public PageData getHangAccountDetail(String FinId, Integer pageSize, Integer pageIndex) {
        Map params = new HashMap();
        String sql = "SELECT\n" +
                " a.账目日期 单据日期,\n" +
                " a.账目单号 挂账单号,\n" +
                " a.挂账类别 挂账类别,\n" +
                " a.账目金额 挂账金额,\n" +
                " ( SELECT IFNULL(SUM(b.账目金额), 0) FROM 销售财务账目表 b WHERE b.关联编号 = a.账目编号 AND b.账目类型 = '核销' AND b.是否红冲=0 ) '核销金额',\n" +
                " a.备注,\n" +
                " IF(a.是否红冲='1','是','否') 是否红冲," +
                " b.人员姓名 操作人,\n" +
                " CASE\n" +
                "WHEN a.同步状态 = '1' THEN\n" +
                " '成功'\n" +
                "ELSE\n" +
                " '未传送'\n" +
                "END 传送结果,\n" +
                " a.同步日期 传送日期,\n" +
                " a.操作日期 操作时间\n" +
                "FROM\n" +
                " `销售财务账目表` a\n" +
                "LEFT JOIN `用户` b ON a.创建用户 = b.用户名\n" +
                "WHERE\n" +
                " 账目类型 = '挂账'\n" +
                "AND 财务编号 = :FinId  ";
        params.put("FinId",FinId);
        return this.getPage(sql,params,pageSize,pageIndex);
    }

    @Override
    public PageData getInvoiceDetail(String quotedNum, Integer pageSize, Integer pageIndex) {
        List<String> typeList = new ArrayList<>();
        typeList.add(FinanceConstant.FAPIAO);
        String sql = "select \n" +
                " b.财务编号,\n" +
                " b.账目日期 发票日期, \n" +
                " b.账目单号 登记单号,\n" +
                " b.账目类型,\n" +
                " b.发票项目,\n" +
                " b.发票类型,\n" +
                " b.发票抬头,\n" +
                " b.纳税人识别号,\n" +
                " b.客户电话 电话,\n" +
                " b.客户开户行 开户行,\n" +
                " b.客户账号 账号,\n" +
                " b.客户地址 地址,\n" +
                " b.账目金额 发票金额,\n" +
                " b.交易凭证号 发票号,\n" +
                " b.备注,\n" +
                " case b.是否红冲 when '1' then '是' else '否' end 是否红冲,\n" +
                " d.人员姓名 操作人,\n" +
                " case b.同步状态  when '1' then '成功' when '2' then '失败' else '未传送' end 传送结果,\n" +
                " b.同步日期 传送日期,\n" +
                " b.操作日期 操作时间\n" +
                "from 销售财务表 a \n" +
                "left join 销售财务账目表 b on a.财务编号 = b.`财务编号`\n" +
                "join 报价单表 c on a.订单编号 = c.初始报价单号\n" +
                "join 用户 d on b.修改用户 = d.用户名 \n" +
                "where c.报价编号 = :quotedNum\n" +
                "and b.账目类型 in (:typeList)" +
                "order by b.修改时间 desc,b.创建时间 desc\n";
        Map params = new HashMap();
        params.put("quotedNum",quotedNum);
        params.put("typeList",typeList);
        return this.getPage(sql,params,pageSize,pageIndex);
    }


    @Override
    public PageData getFinanceReceivablesList(FinanceReceivablesBody.NewCarReceivablesListBody body) {
        String sql = "select \n" +
                " a.*,a.orderTotalPrice - a.payOrderPriceTemp payOrderPrice\n" +
                "from (\n" +
                "  select \n" +
                "  b.*,\n" +
                "  round(IFNULL(c.保险总价,0)+IFNULL(c.车船税,0)+IFNULL(i.购置税,0)+IFNULL(d.上牌服务费,0.0)+IFNULL(d.其他服务费,0)+IFNULL(g.金融服务费,0)+   \n" +
                "  IFNULL(f.延保折后价,0)+IFNULL(e.精品折后价,0)+ifnull(a.折后价,0)+   \n" +
                "  IFNULL(h.置换手续费,0)+IFNULL(f.厂家延保实收,0)+IFNULL(f.第三方延保实收,0)+IFNULL(i.代金券,0)+   \n" +
                "  IFNULL(i.会员费,0)+IFNULL(i.续保服务费,0)+IFNULL(i.贷款抵押费,0)+IFNULL(i.二手车押金,0)+   \n" +
                "  IFNULL(i.上牌保证金,0)+IFNULL(i.车辆管理费,0)+IFNULL(i.常规保养,0)+IFNULL(i.其他六,0)+   \n" +
                "  IFNULL(i.其他七,0)+IFNULL(i.其他一,0)+IFNULL(i.其他二,0)+IFNULL(i.其他三,0)+IFNULL(i.其他四,0)+IFNULL(i.其他五,0),2) orderTotalPrice, " +
                "  CASE WHEN j.收款状态 = '01' THEN '已完款' ELSE '未完款'  END AS paymentStatus,  " +
                "  CASE WHEN j.开票状态 = '01' THEN '已完成' ELSE '未完成'  END AS ticketStatus  " +
                "  from(\n" +
                "    SELECT a.报价编号 as quoteNo, \n" +
                "     a.初始报价单号 as initQuoteNo, \n" +
                "     a.许可 as permit, \n" +
                "     case\n" +
                //"     when c.订单状态 = '合同C' and not isnull(i.合同签约日期) then '合同' \n" +
                "     when c.订单状态 = '意向订单C' and not isnull(c.订单审批通过日期) and date(c.订单审批通过日期) >= ifnull(c.订单失销日期,'1970-01-01') and c.订金类型 = '意向金'  then '意向订单'\n" +
                "     when c.订单状态 = '订单C' and not isnull(c.订单审批通过日期) and date(c.订单审批通过日期) >= ifnull(c.订单失销日期,'1970-01-01') and c.订金类型 = '订金' then '订单'\n" +
                "     when c.订单状态 is null or c.订单状态 = '退订C' then '退订单'\n" +
                "     else '合同'\n" +
                "     end as orderType,\n" +
                "     d.`顾客姓名` as customerName, \n" +
                "     d.`电话号码` as mobile, \n" +
                "     a.`客户类型` as customerType, \n" +
                "     e.身份证 as cardId, \n" +
                "     f.车架号 as vin, \n" +
                "     g.`人员姓名` as scName, \n" +
                "     IFNULL((select sum(账目金额) from 销售财务账目表 as i where i.订单编号 = a.`初始报价单号` and i.账目类型 in('收款','红冲收款')),0) -  \n" +
                "     IFNULL((select sum(账目金额) from 销售财务账目表 as ia where ia.订单编号 =a.`初始报价单号` and ia.账目类型 in('退款','红冲退款')),0) as trueOrderPrice, -- 实收金额  \n" +
                "     IFNULL((select sum(账目金额) from 销售财务账目表 as id where id.订单编号 = a.`初始报价单号` and id.账目类型 in('收款','红冲收款')),0) -  \n" +
                "     IFNULL((select sum(账目金额) from 销售财务账目表 as ie where ie.订单编号 = a.`初始报价单号` and ie.账目类型 in('退款','红冲退款')),0)+\n" +
                "     IFNULL((select sum(账目金额) from 销售财务账目表 as ih where ih.订单编号 =a.`初始报价单号` and ih.账目类型 in('挂账','红冲挂账')),0) as payOrderPriceTemp, -- （收款-退款+挂账）\n" +
                "     IFNULL((select sum(账目金额) from 销售财务账目表 as ii where ii.订单编号 =a.`初始报价单号` and ii.账目类型 in('退款','红冲退款')),0) as refundOrderPrice,  --  已退金额\n" +
                "     IFNULL((select sum(账目金额) from 销售财务账目表 as ij where ij.订单编号 =a.`初始报价单号` and ij.账目类型 in('挂账','红冲挂账')),0) as billOrderPrice,  -- 挂账金额\n" +
                "     IFNULL((select sum(账目金额) from 销售财务账目表 as ik where ik.订单编号 =a.`初始报价单号` and ik.账目类型 in('发票','红冲发票')),0) as invoiceOrderPrice,  -- 开票金额\n" +
                "     IF(i.`交车日期` is NULL,'未交车','已交车') as leaveCarStatus\n" +
                "    FROM 报价单表 as a\n" +
                "    join (select max(a.报价编号) 报价编号 from 报价单表 a,报价订单表 b where a.订单编号 = b.订单编号 and a.许可 = :permit and b.订单审批通过日期 is not null group by 初始报价单号) j on a.报价编号 = j.报价编号\n" +
                "    LEFT JOIN `报价订单表` as c on a.`订单编号` = c.`订单编号` \n" +
                "    LEFT JOIN `顾客记录汇总` as d  on a.`顾客编号` = d.`顾客编号` \n" +
                "    LEFT JOIN `报价客户表` as e on a.`客户详细` = e.`编号` \n" +
                "    LEFT JOIN `lsh库存表` as f on a.`库存编号` = f.`库存编号` \n" +
                "    LEFT JOIN `用户` as g on a.`销售顾问` = g.`用户名`\n" +
                "    LEFT JOIN 报价交车表 as i on a.`交车` = i.`编号` \n" +
                "  ) b\n" +
                "  join 报价单表 a on b.quoteNo = a.报价编号\n" +
                "  LEFT JOIN (  \n" +
                "  SELECT SUM( IF ( `险种` = '交强险', `折后价`, 0 )) + SUM( IF ( `险种` = '交强险', `不计免赔`, 0 )) AS 交强险,  \n" +
                "   SUM( IF ( `险种` = '车船税', `折后价`, 0 )) + SUM( IF ( `险种` = '车船税', `不计免赔`, 0 )) AS 车船税,  \n" +
                "   SUM( IF ( `险种` <> '交强险'&&`险种`<>'车船税', `折后价`, 0 )) + SUM( IF ( `险种` <> '交强险'&&`险种`<>'车船税', `不计免赔`, 0 )) AS 商业险,  \n" +
                "   SUM( IF ( `险种` != '车船税', `折后价`, 0 )) + SUM( IF ( `险种` != '车船税', `不计免赔`, 0 )) AS 保险总价,保险编号 \n" +
                "  FROM `报价保险详细表` group by 保险编号 \n" +
                "  ) c ON a.保险编号 = c.保险编号   \n" +
                "  LEFT JOIN (select MAX(case when 费用名称 = '上牌服务费' then 费用折后价 end) 上牌服务费 ,      \n" +
                "  MAX(case when 费用名称 = '其他服务费' then 费用折后价 end) 其他服务费,      \n" +
                "  费用编号      \n" +
                "  from 报价费用详细表 GROUP BY 费用编号      \n" +
                "  ) d ON a.费用编号 = d.费用编号      \n" +
                "  LEFT JOIN 报价精品表 e ON  a.精品 = e.编号      \n" +
                "  LEFT JOIN 报价五星延保 f ON f.延保编号 = a.延保编号      \n" +
                "  LEFT JOIN 报价金融表 g ON g.金融编号 = a.金融    \n" +
                "  LEFT JOIN   报价置换表 h ON a.置换编号 = h.置换编号    \n" +
                "  LEFT JOIN 报价预收表 i ON a.预收编号 = i.预收编号\n" +
                "  LEFT JOIN 销售财务表 j ON b.initQuoteNo = j.订单编号 " +
                ") a\n" +
                "where a.permit = :permit\n" ;
        Map params = Maps.newHashMap();
        params.put("permit",body.permit );
        // 查询条件
        if (!Strings.isNullOrEmpty(body.orderType)) {
            sql += "and a.orderType = :orderType \n";
                params.put("orderType", body.orderType);
            }
        if (!Strings.isNullOrEmpty(body.scName)) {
            sql += "and a.scName like :scName \n";
            params.put("scName", "%" + body.scName + "%");
        }
        if (!Strings.isNullOrEmpty(body.leaveCarStatus)) {
            sql += "and a.leaveCarStatus = :leaveCarStatus ";
            params.put("leaveCarStatus",body.leaveCarStatus);
        }
        if (!Strings.isNullOrEmpty(body.cardId)) {
            sql += "and a.cardId like :cardId \n";
            params.put("cardId", "%" + body.cardId + "%");
        }
        if (!Strings.isNullOrEmpty(body.orderNumber)) {
            sql += "and a.initQuoteNo like :orderNumber \n";
            params.put("orderNumber", "%" + body.orderNumber + "%");
        }
        if (!Strings.isNullOrEmpty(body.customerName)) {
            sql += "and a.customerName like :customerName \n";
            params.put("customerName", "%" + body.customerName + "%");
        }
        if (!Strings.isNullOrEmpty(body.mobile)) {
            sql += "and a.mobile like :mobile \n";
            params.put("mobile", "%" + body.mobile + "%");
        }
        if (!Strings.isNullOrEmpty(body.vin)) {
            sql += "and a.vin like :vin \n";
            params.put("vin", "%" + body.vin + "%");
        }
        if (!Strings.isNullOrEmpty(body.paymentStatus)){
            sql += "and a.paymentStatus = :paymentStatus ";
            params.put("paymentStatus", body.paymentStatus);
        }
        if (!Strings.isNullOrEmpty(body.ticketStatus)){
            sql += "and a.ticketStatus = :ticketStatus ";
            params.put("ticketStatus",body.ticketStatus);
        }

        sql += " order by a.quoteNo desc ";
        return this.getPage(sql, params, body.pageSize ,body.page);
    }

    @Override
    public List getReceivablesInfoByCar(String vin) {
        String sql =" SELECT m.品牌 as brand,\n" +
                " m.`级别` as series,\n" +
                " m.`车型` as model,\n" +
                " m.`车型详细` as style,\n" +
                " s.`颜色` as color,\n" +
                " s.车架号 as vin,\n" +
                " IF(j.`交车日期` IS NULL,\"未交车\",\"已交车\") as leaveCarState\n" +
                " from `lsh库存表` as s \n" +
                " LEFT JOIN lsh车型 as m on s.`车型编号` = m.`车型编号`\n" +
                " LEFT JOIN 报价单表 as o on s.`库存编号` = o.`库存编号`\n" +
                " LEFT JOIN 报价交车表 as j on o.`交车` = j.`编号`\n" +
                " WHERE\n" +
                " s.`车架号` = :vin \n" +
                " ORDER BY  o.`报价编号` DESC\n" +
                " LIMIT 0,1";
        Map params = Maps.newHashMap();
        params.put("vin",vin);
        return this.getList(sql,params);
    }

    @Override
    public List getReceivablesInfoByOrder(String initQuoteNo) {
        String sql = " SELECT a.`报价编号` as quoteNo,\n" +
                " case\n" +
                "  when o.订单状态 = '意向订单C' and not isnull(o.订单审批通过日期) and date(o.订单审批通过日期) >= ifnull(o.订单失销日期,'1970-01-01') and o.订金类型 = '意向金'  then '意向订单'\n" +
                "  when o.订单状态 = '订单C' and not isnull(o.订单审批通过日期) and date(o.订单审批通过日期) >= ifnull(o.订单失销日期,'1970-01-01') and o.订金类型 = '订金' then '订单'\n" +
                "  when o.订单状态 is null or o.订单状态 = '退订C' then '退订单'\n" +
                "  else '合同'\n" +
                " end as orderType,\n" +
                " a.`初始报价单号` as initQuoteNo,\n" +
                " o.`销售类型` as saleType,\n" +
                " a.`客户类型` as customerType,\n" +
                " qu.名称 as customerName,\n" +
                " qu.电话 as mobile,\n" +
                " qu.身份证 as cardId,\n" +
                " j.`企业名称` as company,\n" +
                " u.`人员姓名` as scName\n" +
                " from `报价单表` as a\n" +
                " LEFT JOIN 报价订单表 as o on a.`订单编号` = o.`订单编号`\n" +
                " LEFT JOIN 报价客户表 as qu on qu.`编号` = a.`客户详细`\n" +
                " LEFT JOIN 报价交车表 as j on j.`编号` = a.`交车`\n" +
                " LEFT JOIN 用户 as u on u.`用户名` = a.`销售顾问`\n" +
                " WHERE\n" +
                " a.`初始报价单号` = :initQuoteNo \n" +
                " ORDER BY  a.`报价编号` DESC\n" +
                " LIMIT 0,1";
        Map params = Maps.newHashMap();
        params.put("initQuoteNo",initQuoteNo);
        return this.getList(sql,params);
    }

    @Override
    public List getReceivablesInfoByMoney(String initQuoteNo) {
        String sql = " SELECT \n" +
                " a.报价编号 as quoteNo,\n" +
                " a.折后价 as carMsrp,##车身价\n" +
                " IFNULL(i.购置税,0) as purchaseTax,##购置税\n" +
                " IFNULL(b.订金金额,0) + IFNULL(b.订金加减金额,0) - IFNULL(b.`退订订金金额`,0)  as depositPrice,##订金金额\n" +
                " IFNULL(d.上牌服务费,0.00) as onTheCardPrice, ##上牌费\n" +
                " IFNULL(e.精品折后价,0) as boutiquePrice,##精品费\n" +
                " round(IFNULL(d.其他服务费,0.00),2) as otherServcePrice,##销售服务费\n" +
                " IF(g.金融服务费 is null or g.金融服务费 = '',0.00,round(g.金融服务费,2)) loanServicePrice,##贷款服务费\n" +
                " IFNULL(h.置换手续费,0) displaceServicePrice,##置换手续费\n" +
                " IF(h.`置换估价` is null or h.`置换估价` = '',0.00,CONCAT('-', h.`置换估价`)) as displacePrice,##置换估价值\n" +
                " IFNULL(a.授信额度,0) as creditAmount,##授信挂账额度\n" +
                " IFNULL(c.保险总价,0) as insuranceTotal,##保险费(交强险+商业险)\n" +
                " IFNULL(c.交强险,0) as compulsoryInsurance,##交强险\n" +
                " IFNULL(c.商业险,0) as commercialInsurance,##商业险\n" +
                " IFNULL(c.车船税,0) as vehicleAndVesselTax,##车船税\n" +
                " IFNULL(f.厂家延保实收,0) as vender,##厂家延保\n" +
                " IFNULL(f.第三方延保实收,0) as thirdExtended,##第三方延保\n" +
                " IFNULL(f.延保折后价,0.00)as shop,##本店延保\n" +
                " IFNULL(i.代金券,0)+IFNULL(i.会员费,0)+IFNULL(i.续保服务费,0)+IFNULL(i.贷款抵押费,0)+IFNULL(i.二手车押金,0)+IFNULL(i.上牌保证金,0)+\n" +
                " IFNULL(i.车辆管理费,0)+IFNULL(i.常规保养,0)+IFNULL(i.其他六,0)+IFNULL(i.其他七,0) as prePayment,##预收款\n" +
                " IFNULL(i.其他一,0) as otherOne,##其他一\n" +
                " IFNULL(i.其他二,0) as otherTwo,##其他二\n" +
                " IFNULL(i.其他三,0) as otherThree,##其他三\n" +
                " IFNULL(i.其他四,0) as otherFour,##其他四\n" +
                " IFNULL(i.其他五,0) as otherFive,##其他五\n" +
                " (a.折后价 + IFNULL(i.购置税,0) + IFNULL(d.上牌服务费,0.00)  + IFNULL(e.精品折后价,0) + round(IFNULL(d.其他服务费,0.00),2) + \n" +
                "IF(g.金融服务费 is null or g.金融服务费 = '',0.00,round(g.金融服务费,2))  + IFNULL(h.置换手续费,0) + \n" +
                "IFNULL(c.保险总价,0) + IFNULL(c.车船税,0) + IFNULL(f.厂家延保实收,0) + IFNULL(f.第三方延保实收,0) + IFNULL(f.延保折后价,0.00) + (IFNULL(i.代金券,0)+IFNULL(i.会员费,0)+IFNULL(i.续保服务费,0)+IFNULL(i.贷款抵押费,0)+IFNULL(i.二手车押金,0)+IFNULL(i.上牌保证金,0)+\n" +
                "IFNULL(i.车辆管理费,0)+IFNULL(i.常规保养,0)+IFNULL(i.其他六,0)+IFNULL(i.其他七,0)) + IFNULL(i.其他一,0) + IFNULL(i.其他二,0)+ IFNULL(i.其他三,0)+ IFNULL(i.其他四,0)+ IFNULL(i.其他五,0)) as totalPrice ##总计\n" +
                " FROM `报价单表` as a \n" +
                " LEFT JOIN  报价付款详细 as m ON a.报价编号 = m.`报价编号`\n" +
                " LEFT JOIN 报价预收表 i ON a.预收编号 = i.预收编号\n" +
                " LEFT JOIN 报价订单表 b ON a.订单编号 = b.订单编号 \n" +
                " LEFT JOIN 报价精品表 e ON  a.精品 = e.编号\n" +
                " LEFT JOIN 报价金融表 g ON g.金融编号 = a.金融\n" +
                " LEFT JOIN 报价五星延保 f ON f.延保编号 = a.延保编号 \n" +
                " LEFT JOIN   报价置换表 h ON a.置换编号 = h.置换编号 \n" +
                " LEFT JOIN (select MAX(case when 费用名称 = '上牌服务费' then 费用折后价 end) 上牌服务费 , \n" +
                "                  MAX(case when 费用名称 = '其他服务费' then 费用折后价 end) 其他服务费, 费用编号    \n" +
                "           from 报价费用详细表 GROUP BY 费用编号) as d ON a.`费用编号` = d.`费用编号` \n" +
                " LEFT JOIN (SELECT SUM( IF ( `险种` = '交强险', `折后价`, 0 )) + SUM( IF ( `险种` = '交强险', `不计免赔`, 0 )) AS 交强险,\n" +
                "              SUM( IF ( `险种` = '车船税', `折后价`, 0 )) + SUM( IF ( `险种` = '车船税', `不计免赔`, 0 )) AS 车船税,\n" +
                "              SUM( IF ( `险种` <> '交强险'&&`险种`<>'车船税', `折后价`, 0 )) + SUM( IF ( `险种` <> '交强险'&&`险种`<>'车船税', `不计免赔`, 0 )) AS 商业险,\n" +
                "              SUM( IF ( `险种` != '车船税', `折后价`, 0 )) + SUM( IF ( `险种` != '车船税', `不计免赔`, 0 )) AS 保险总价,保险编号 \n" +
                "\t\t\t\t\t\t\tFROM `报价保险详细表` WHERE `保险编号` = (select 保险编号 from 报价单表 where 报价编号 = (select max(报价编号) from 报价单表 where `初始报价单号` =:initQuoteNo ))) c ON a.保险编号 = c.保险编号\n" +
                " where \n" +
                " a.初始报价单号= :initQuoteNo    \n" +
                " ORDER BY a.报价编号 DESC\n" +
                " limit 0,1\n";
        Map params = Maps.newHashMap();
        params.put("initQuoteNo",initQuoteNo);
        return this.getList(sql,params);
    }

    @Override
    public List getReceivablesInfoByOperation(String initQuoteNo) {
        String sql = " SELECT \n" +
                " a.初始报价单号 as initQuoteNo,\n" +
                " a.报价编号 as quoteNp,\n" +
                " o.`销售类型` as saleType,\n" +
                " IFNULL((select sum(账目金额) from 销售财务账目表 where  订单编号 =:initQuoteNo and 账目类型=\"收款\"),0) as receivables,\n" +
                " IFNULL((select sum(账目金额) from 销售财务账目表 where  订单编号 =:initQuoteNo and 账目类型=\"退款\"),0)as refund,\n" +
                " IFNULL((select sum(账目金额) from 销售财务账目表 where  订单编号 =:initQuoteNo and 账目类型=\"挂账\"),0) as bill,\n" +
                " IFNULL((select sum(账目金额) from 销售财务账目表 where  订单编号 =:initQuoteNo and 账目类型=\"发票\"),0) as invoice,\n" +
                " IFNULL((select sum(账目金额) from 销售财务账目表 where  订单编号 =:initQuoteNo and 账目类型=\"核销\"),0) as writeOf\n" +
                " from  报价单表 as a\n" +
                " LEFT JOIN 销售财务账目表 as f on a.`初始报价单号` = f.`订单编号`\n" +
                " LEFT JOIN 报价订单表 as o on a.`订单编号` = o.`订单编号`\n" +
                " WHERE\n" +
                " a.初始报价单号 = :initQuoteNo \n" +
                " ORDER BY a.报价编号 DESC\n" +
                " limit 0,1\n";
        Map params = Maps.newHashMap();
        params.put("initQuoteNo",initQuoteNo);
        return this.getList(sql,params);
    }

    @Override
    public List getIdListByAmountType(String typeName, String initQuoteNo) {
        String sql = " SELECT 账目编号 from `销售财务账目表`  where  `订单编号` =:initQuoteNo and 账目类型 =:typeName ";
        Map params = Maps.newHashMap();
        params.put("initQuoteNo",initQuoteNo);
        params.put("typeName",typeName);
        return this.getList(sql,params);
    }

    @Override
    public List getRedPushById(List idList) {
        String sql = " SELECT IFNULL(SUM(账目金额),0) as redPushTotal from `销售财务账目表`  where  账目类型 like \"%红冲%\" and `关联编号` in(:idList)";
        Map params = Maps.newHashMap();
        params.put("idList",idList);
        return this.getList(sql,params);
    }

    @Override
    public List getReceivablesInfoNo(String initQuoteNo) {
        String sql = " select a.`报价编号` as quoteNo,\n" +
                " a.`初始报价单号` as initQuoteNo,\n" +
                " b.`财务编号` as financeNo,\n" +
                " b.`财务状态` as financeState \n" +
                " from 报价单表 as a\n" +
                " LEFT JOIN 销售财务表 as b ON a.`初始报价单号` = b.`订单编号`\n" +
                " WHERE\n" +
                " a.`初始报价单号` =:initQuoteNo \n" +
                " ORDER BY a.`报价编号` DESC\n" +
                " LIMIT 0,1";
        Map params = Maps.newHashMap();
        params.put("initQuoteNo",initQuoteNo);
        return this.getList(sql,params);
    }


    @Override
    public List getQuotaMoney( String quotedNum) {
        String sql = "SELECT\n" +
                "a.授信额度 临时授信," +
                "b.贷款额 金融贷款,\n" +
                "c.置换估价 二手车置换\n" +
                "FROM\n" +
                "报价单表 a\n" +
                "LEFT JOIN 报价金融表 b ON a.金融=b.金融编号\n" +
                "LEFT JOIN 报价置换表 c ON a.置换编号=c.置换编号\n" +
                "WHERE\n" +
                "a.报价编号 = :quotedNum ";
        Map params = Maps.newHashMap();
        params.put("quotedNum",quotedNum);

        return this.getList(sql,params);
    }

    @Override
    public List getBoutiqueDetails(String quotedNum) {
        String sql = "select \n" +
                " b.报价编号,\n" +
                " case a.origin_type when 1 then a.origin_shop_id else (select 产品编号 from lsh套餐信息 where 套餐编号 = a.ID) end as 精品编码,\n" +
                " a.名称 精品名称,\n" +
                " a.数量,\n" +
                " a.`原价`,\n" +
                " round(a.折后价,2) 销售单价,\n" +
                " round(a.数量*a.折后价,2) 销售金额,\n" +
                " round(sum(a.数量*a.折后价),2) 总价\n" +
                "from 报价精品详细表 a \n" +
                "join 报价单表 b on a.编号 = b.精品\n" +
                "where b.报价编号 = :quotedNum\n" +
                "group by a.名称\n";
        Map params = Maps.newHashMap();
        params.put("quotedNum",quotedNum);
        return this.getList(sql,params);
    }

    @Override
    public List getInsuranceDetails(String quotedNum) {
        String sql = "select \n" +
                "b.保险编号, \n" +
                "a.险种,\n" +
                "a.参数 '首年保额',\n" +
                "a.折后价 '首年保费',\n" +
                "a.不计免赔\n" +
                "from 报价保险详细表 a\n" +
                "join 报价单表 b on a.保险编号 = b.保险编号\n" +
                "where a.第N年 = '1' \n" +
                "and b.报价编号 = :quotedNum\n" +
                "and a.险种 <> '商业险'\n" +
                "order by FIELD(a.险种,'交强险','商业三者责任险','车辆损失险','车上人员责任险 - 乘客','车上人员责任险 - 司机'," +
                " '车身划痕损失险','玻璃单独破碎险','机动车损失保险无法找到第三方特约险','发动机涉水损失险','全车盗抢险','车船税'," +
                " '自燃损失险','修理期间费用补偿险','倒车镜、车灯单独损失险','指定修理厂险','新增设备损失险','精神损害抚慰金责任险','自定义险种一','自定义险种二','附加险','车上货物责任险')";  // 按照指定的险种排序
        Map params = Maps.newHashMap();
        params.put("quotedNum",quotedNum);
        return this.getList(sql,params);
    }

    @Override
    public List getInsuranceInfo(String quotedNum) {
        String sql = "select \n" +
                "  b.报价编号,\n" +
                "  a.保险编号,\n" +
                "  a.交强险保险公司,\n" +
                "  a.交强险投保期,\n" +
                "  round(if(c.险种 = '交强险' and c.第N年 = '1',c.折后价,null),2) 交强险总价,\n" +
                "  a.商业险保险公司, \n" +
                "  if(ifnull(a.商业险保险公司,'')='',null,a.一保N年) 商业险投保期,\n" +
                "  if(ifnull(a.商业险保险公司,'')='',null,round(sum(if(c.险种 <> '交强险' and c.险种 <> '车船税' and c.第N年 = '1' and c.不计免赔 is not null,c.不计免赔,0)) + \n" +
                "    sum(if(c.险种 <> '交强险' and c.险种 <> '车船税' and c.第N年 = '1' and c.折后价 is not null,c.折后价,0)),2)) as 首年商业险总价,\n" +
                "  if(ifnull(a.商业险保险公司,'')='',null,round(sum(if(c.险种 <> '交强险' and c.险种 <> '车船税' and c.不计免赔 is not null,c.不计免赔,0)) + \n" +
                "    sum(if(c.险种 <> '交强险' and c.险种 <> '车船税' and c.折后价 is not null,c.折后价,0)),2)) as 一保多年商业险总价\n" +
                "from 报价保险表 a\n" +
                "join 报价单表 b on a.保险编号 = b.保险编号\n" +
                "join 报价保险详细表 c on a.保险编号 = c.保险编号\n" +
                "where b.报价编号 = :quotedNum";
        Map params = Maps.newHashMap();
        params.put("quotedNum",quotedNum);
        return this.getList(sql,params);
    }
}
